import pandas as pd
from openpyxl import load_workbook
import random
import keyboard, sys, time

def read_excel_with_merged_cells(file_path):
    # 加载 Excel 文件
    wb = load_workbook(file_path)
    # 获取第一个工作表
    sheet = wb.active

    # 处理合并单元格
    merged_ranges = list(sheet.merged_cells.ranges)
    print(f'共有 {len(merged_ranges)}个合并单元')
    num = 0
    for merged_range in merged_ranges:
        min_col, min_row, max_col,  max_row = merged_range.bounds        
        top_left_value = sheet.cell(row=min_row, column=min_col).value

        # 取消合并单元格
        try:
            sheet.unmerge_cells(start_row=min_row, start_column=min_col, end_row=max_row, end_column=max_col)            
        # 用原合并单元格的值填充分解后的单元格
            for row in range(min_row, max_row + 1):
                for col in range(min_col, max_col + 1):
                    sheet.cell(row=row, column=col, value=top_left_value)
        except Exception as e:
            num += 1
            print(f'{num}. {e}')
            
    # 将处理后的工作表数据读取到 DataFrame
    data = sheet.values
    columns = next(data)
    df = pd.DataFrame(data, columns=columns)
    return df

# 使用当前时间作为随机数种子
random.seed(time.time())

# file_path = 'data2.xlsx'
# df = read_excel_with_merged_cells(file_path)
# 直接从 processed.xlsx 读取数据
file_path = 'BDC.xlsx'
df = pd.read_excel(file_path)

index = 0
total = 0
status = 'meaning_shown'

def show_word():
    global index, num, status, wordList, total    
    if status == 'word_shown':        
        print(f'意义： {df.iloc[num, 1]} \n')
        status = 'meaning_shown'
    elif status == 'meaning_shown':
        num = random.randint(0, 200)
        wordList.append(num)
        print(f'\n[{num}] {df.iloc[num, 0]}  熟练度：{df.iloc[num, 6]} ')
        print('例句：', df.iloc[num, 2])
        print('  1：懂  2：不太懂  3：不懂')        
        status = 'word_shown'

def mark(index): 
    global num, total
    mark = 1
    try:
        total += 1
        if index == 1:
            print('懂 +1')
            print('____________________________________________________________________________')
        if index == 2:
            print('不太懂 +1') 
            print('____________________________________________________________________________')
        if index == 3:
            print('不懂 +1')
            print('____________________________________________________________________________')
        try:
            value = int(df.iloc[num, index+2])
            df.iloc[num, index+2] = int(value) + 1
            A = int(df.iloc[num, 3])
            B = int(df.iloc[num, 4])
            C = int(df.iloc[num, 5])
            df.iloc[num, 6] = (A + B*0.5 ) / (A + B + C) 
        except:
            df.iloc[num, index+2] = 1

        try:
            df.to_excel('processed.xlsx', index=False)
        except Exception as e:
            print('数据文件被占用')
        show_word() 
    except Exception as e:
        print(e)

def markerror():
    print('错误上报 [num]')
    try:
        df.df.iloc[num, 7] = '用户报错'
        df.to_excel('processed.xlsx', index=False)
    except Exception as e:
        print('数据文件被占用')
    print('_____________________________')

def mark1():  
    mark(1)    

def mark2():  
    mark(2)
    

def mark3():  
    mark(3)

def quit_program():
    global total
    print(f'\n 今天总共复习了{total}个单词，熟练程度如下：')
    for num in wordList:
        print(f'[{num}] {df.iloc[num, 0]}  熟练度：{df.iloc[num, 6]} ')
    print("\n继续加油哦")
    sys.exit()  

num = 0
total = 0
wordList = []
# 绑定按键和对应的函数
keyboard.add_hotkey(' ', show_word)
keyboard.add_hotkey('1', mark1)
keyboard.add_hotkey('2', mark2)
keyboard.add_hotkey('3', mark3)
keyboard.add_hotkey('e', markerror)
keyboard.add_hotkey('q', quit_program)
print('\n---------------------')
print('--   背单词  v0.2   --')
print('----------------------')
print("按 空格 键显示单词，再按显示意义和例句，按 Q 键退出程序。\n")

keyboard.wait()